/********************************************************************************************************

Script:			Exceptions
Author:			Richard Slim
Date:			12/12/2012
Description:	This script will process all exceptions stored in the exceptions table

Usage:			This is called from within the main SSIS task

2013-12-02		RSlim		Added the CleanupTime exception
2013-12-27		RSlim		Added the DisableJob Exception type
2014-03-24		RSlim		Added the Change Job Schedule Exception
				
********************************************************************************************************/

DECLARE @ExceptionType varchar(20)
DECLARE @ExceptionValue varchar(300)
DECLARE @ExceptionValue2 varchar(300)
DECLARE @Command VARCHAR(MAX)

SET @ExceptionType = ?
SET @ExceptionValue = ?
SET @ExceptionValue2 = ?

BEGIN

/*************************************Disable Job*******************************************************/
	IF @ExceptionType = 'DisableJob'
	BEGIN
		EXEC msdb.dbo.sp_update_job
		@job_name = @ExceptionValue,
		@enabled = 0; 
	END  
/******************************************************************************************************/

/*************************************Database Add/Remove**********************************************/
	IF @ExceptionType = 'DatabaseAddRemove'
	BEGIN

		-- Declare the variables
		DECLARE @Start int
		DECLARE @End int
		DECLARE @CurrentBackupString VARCHAR(300)

		--Grab the job step command
		SELECT @Command = sjs.command FROM msdb.dbo.sysjobsteps sjs
		JOIN msdb.dbo.sysjobs sj
		ON sjs.job_id = sj.job_id
		WHERE sjs.step_id = 1 AND sj.name = @ExceptionValue2

		--Check the Database portion against the exception, if it is different change it
		SELECT @Start = CHARINDEX('''',@Command,0) + 1

		SELECT @End = CHARINDEX('''',@Command,@Start) - @Start

		SELECT @CurrentBackupString = SUBSTRING(@Command,@Start,@End)

		IF @CurrentBackupString <> @ExceptionValue
		SELECT @Command = REPLACE(@Command, @CurrentBackupString, @ExceptionValue)

		--Modify the job with the new command
		EXEC msdb..sp_update_jobstep @job_name = @ExceptionValue2, @step_id = '1', @command = @Command

	END
/******************************************************************************************************/  

/***************************************Cleanup Time***************************************************/
	IF @ExceptionType = 'CleanupTime'
	BEGIN
		
		--Declare variable and set the cleanup time portion of the string
		DECLARE @String VARCHAR(100)
		SET @String = '@CleanupTime = ' + @ExceptionValue

		--grab the full command from the jobs system table and replace the cleanup time portion
		SELECT @Command = REPLACE(sjs.command,'@CleanupTime = 24',@String) FROM msdb.dbo.sysjobsteps sjs
		JOIN msdb.dbo.sysjobs sj
		ON sjs.job_id = sj.job_id
		WHERE sjs.step_id = 1 AND sj.name = @ExceptionValue2

		--Update the job
		EXEC msdb..sp_update_jobstep @job_name = @ExceptionValue2, @step_id = '1', @command = @Command

	END  
/******************************************************************************************************/

/*************************************Change Job Sched*************************************************/
/*

Format:
@ExceptionValue = The name of the job
@ExceptionValue2 = DayPartTimePartIntervalPart

e.g. a valid String for Weekly Mon 11pm would be: Mon2300W

Day Part:
Sun, Mon Tue, Wed, Thu, Fri, Sat

Time Part (24 Hours):
HHMM

Interval Part
D, W, H

*/

IF @ExceptionType = 'ChangeJobSched'
	BEGIN
  
		--Declare some variables  
		DECLARE @SchedName		VARCHAR(100)
		DECLARE @DayPart		VARCHAR(3)
		DECLARE @DayVal			INT
		DECLARE @TimePart		VARCHAR(4)
		DECLARE @TimeVal		INT
		DECLARE @IntervalPart	VARCHAR(2)
		DECLARE @StartTime		INT
		DECLARE @EndTime		INT
		DECLARE @StartDate		INT

		--Grab the name of the schedule we will be editing
		SELECT @SchedName = ss.name FROM msdb.dbo.sysschedules ss
			JOIN msdb.dbo.sysjobschedules sjs ON ss.schedule_id = sjs.schedule_id
			JOIN msdb.dbo.sysjobs sj ON sjs.job_id = sj.job_id
			WHERE sj.name = @ExceptionValue

		--Grab the DayPart of the value and assign it its stored proc value
		SET @DayPart = SUBSTRING(@ExceptionValue2,1,3)
		SET @DayVal = CASE WHEN @DayPart = 'Sun' THEN 1
							WHEN @DayPart = 'Mon' THEN 2
							WHEN @DayPart = 'Tue' THEN 4
							WHEN @DayPart = 'Wed' THEN 8
							WHEN @DayPart = 'Thu' THEN 16
							WHEN @DayPart = 'Fri' THEN 32
							WHEN @DayPart = 'Sat' THEN 64
					END
		--grab the Time part
		SET @TimePart = SUBSTRING(@ExceptionValue2,4,4)
		SET @TimeVal = CAST(@TimePart AS INT) * 100

		--Grab the interval Part
		SET @IntervalPart = SUBSTRING(@ExceptionValue2,8,1)

		--SET @IntervalPart = CASE	WHEN @IntervalPart = 'W' THEN '8'
		--							WHEN @IntervalPart = 'D' THEN '4'
		--							WHEN @IntervalPart = 'M' THEN '16'
		--							WHEN @IntervalPart = 'H' THEN '0x8'
		--					END
		
		--Now build the update schedule command depending on what Interval is chosen
		IF @IntervalPart = 'W'
		BEGIN
		
			EXEC msdb..sp_update_schedule
			@name = @SchedName,
			@freq_type = 8,
			@freq_interval = @DayVal,
			@freq_recurrence_factor = 1,
			@active_start_time = @TimeVal
		
		END
		ELSE IF @IntervalPart = 'D'
		BEGIN
		
			EXEC msdb..sp_update_schedule
			@name = @SchedName,
			@freq_type = 4,
			@freq_interval = 1,
			@active_start_time = @TimeVal
		
		END      
		--ELSE IF @IntervalPart = 'M'
		--BEGIN
		--END
		ELSE IF @IntervalPart = 'H'
		BEGIN
		
			SET @TimePart = SUBSTRING(@TimePart,1,2) + ':' + SUBSTRING(@TimePart,3,2)
			SET @EndTime = cast(REPLACE(RIGHT(CONVERT(VARCHAR(20),DATEADD(mi,-1,@TimePart),126),8),':','') as int)
			SET @StartDate = 20140101 

			EXEC msdb..sp_update_schedule
			@name = @SchedName,
			@freq_type  = 4,
			@freq_interval = 1,
			@freq_subday_type  = 8,
			@freq_subday_interval = 1,
			@active_start_time = @TimeVal,
			@active_end_time = @EndTime,
			@active_start_date = @StartDate
		
		END         
	
	END 
/******************************************************************************************************/

END